package com.crm.dao;


	
	
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;

import com.crm.entity.Opportunity;
import com.crm.entity.QuotParts;
import com.crm.entity.Quotation;
import com.crm.util.ApplicationContext;

	public class QuotPartsDAO {
		protected final Log logger = LogFactory.getLog(getClass());

		
		public  int insert(QuotParts cl)throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			Transaction tx=null;
			try {
				tx = session.beginTransaction();
				session.saveOrUpdate(cl);
				tx.commit();
				return 0;
			}
			catch (Exception e) {
				if (tx!=null) tx.rollback();
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  int insertList(List<QuotParts> lqp)throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			Transaction tx=null;
			try {
				tx = session.beginTransaction();
				for(QuotParts qp:lqp){
					session.saveOrUpdate(qp);
				}
				tx.commit();
				return 0;
			}
			catch (Exception e) {
				if (tx!=null) tx.rollback();
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  int update(QuotParts c) throws Exception{
			Session session =  ApplicationContext.getHiberSession().openSession();
			Transaction tx=null;
			try {
				tx = session.beginTransaction();
				session.saveOrUpdate(c);
				tx.commit();
				return 0;
			}
			catch (Exception e) {
				if (tx!=null) tx.rollback();
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  int delete(QuotParts c) throws Exception{
			Session session =  ApplicationContext.getHiberSession().openSession();
			Transaction tx=null;
			try {
				tx = session.beginTransaction();
				session.delete(c);
				tx.commit();
				return 0;
			}
			catch (Exception e) {
				if (tx!=null) tx.rollback();
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  List<QuotParts> findAll(int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				 List<QuotParts> custObjs=new ArrayList<QuotParts>();
				 List objs=null;
				Query query=session.createQuery("from QuotParts and business_id=:busid");
				query.setInteger("busid", busid);
				objs=query.list();
				for(Object o:objs){
					custObjs.add((QuotParts)o);
				}
				return custObjs;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  QuotParts findById(int partid,int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qrystr="From QuotParts  where partid=:pid and business_id=:busid";
				Query qry=session.createQuery(qrystr);
				qry.setInteger("pid", partid);
				qry.setInteger("busid", busid);
				Object ob=qry.uniqueResult();
				QuotParts p=(QuotParts)ob;
				return p;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  QuotParts findQPbyId(int i,int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qrystr="From QuotParts  where id=:pid and business_id=:busid";
				Query qry=session.createQuery(qrystr);
				qry.setInteger("pid", i);
				qry.setInteger("busid", busid);
				Object ob=qry.uniqueResult();
				QuotParts p=(QuotParts)ob;
				return p;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}
		}

		public  QuotParts getByName(int enqid,String name,int busid) throws HibernateException, Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String querystr="From QuotParts where enqid=:enqid and prtnum=:name or description=:name and business_id=:busid";
				Query query=session.createQuery(querystr);
				query.setInteger("busid", busid);
				query.setInteger("enqid", enqid);
				query.setString("name", name);
				Object queryresult=query.uniqueResult();
				QuotParts comp=(QuotParts)queryresult;
				return comp;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  List<QuotParts> getQuotParts(String ref,int busid)throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qs="from QuotParts p where p.enqid=(select id from Opportunity where ref_num=:ref and business_id=:busid) and business_id=:busid  order by partid";
				Query qr=session.createQuery(qs);
				qr.setString("ref", ref);
				qr.setInteger("busid", busid);
				List<QuotParts>  parts=new ArrayList<QuotParts>();
				List<Object> res=qr.list();
				if(res!=null)
					for(Object o:res){
						parts.add((QuotParts)o);
					}
				return parts;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}

		}
		
		public  List<QuotParts> getQuotPartsBySources(int enqid,int sourceid,int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String querystr="From PartSources ps where ps.enqid=:enqid and ps.sourceid=:sourceid and business_id=:busid   order by partid";
				Query query=session.createQuery(querystr);
				query.setInteger("enqid", enqid);
				query.setInteger("busid", busid);
				query.setInteger("sourceid", sourceid);
				List<QuotParts>  parts=new ArrayList<QuotParts>();
				List<Object> res=query.list();
				if(res!=null)
					for(Object o:res){
						parts.add((QuotParts)o);
					}
				return parts;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}

		}
		public  QuotParts findByPartIdEnqId(int enqid,int partid,int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qrystr="From QuotParts where partid=:pid and enqid=:eid and business_id=:busid  order by partid";
				Query qr=session.createQuery(qrystr);
				qr.setInteger("pid", partid);
				qr.setInteger("busid", busid);
				qr.setInteger("eid", enqid);
				Object ob=qr.uniqueResult();
				QuotParts pr=(QuotParts)ob;
				return pr;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}

		}
		public  List<QuotParts> getPartsByQuot(String qtNum,int enqid,int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qs="from QuotParts  where quotid=(select max(id) from Quotation where quotno=:qtNum and business_id=:busid and enqid=:enqid) and business_id=:busid  order by partid";
				Query qr=session.createQuery(qs);
				qr.setInteger("busid", busid);
				qr.setString("qtNum", qtNum);
				qr.setInteger("enqid",enqid);
				List<QuotParts> p=qr.list();
				return p;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}

		}
		public  List<QuotParts> getPartsByQuotId(int quotid,int enqid,int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qs="from QuotParts  where quotid=:quotid and business_id=:busid  order by partid";
				Query qr=session.createQuery(qs);
				qr.setInteger("busid", busid);
				qr.setInteger("quotid", quotid);
				List<QuotParts> p=(List<QuotParts>)qr.list();
				return p;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}
			
		}
		public  QuotParts findByPartNameEnqId(int enqid, String prtnum,int busid) throws Exception{
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qrystr="From QuotParts  where partid=:partnum and enqid=:eid and business_id=:busid  order by partid";
				Query qr=session.createQuery(qrystr);
				qr.setString("partnum", prtnum);
				qr.setInteger("busid", busid);
				qr.setInteger("eid", enqid);
				Object ob=qr.uniqueResult();
				QuotParts pr=(QuotParts)ob;
				return pr;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}
		}
		public  QuotParts findStock(int partid, String prtnum,int busid) throws Exception{
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qrystr="From QuotParts where partid=:partid and business_id=:busid order by partid";
				Query qr=session.createQuery(qrystr);
				qr.setInteger("partid", partid);
				qr.setInteger("busid", busid);
				Object ob=qr.uniqueResult();
				QuotParts pr=(QuotParts)ob;
				return pr;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}
		}
		
		public  List<QuotParts> getpo(int quotid,int suid,int busid)throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String qtr="From QuotParts where quotid=:quotid and supplier=:suid and business_id=:busid order by partid";
				Query qry=session.createQuery(qtr);
				qry.setInteger("quotid", quotid);
				qry.setInteger("suid", suid);
				qry.setInteger("busid", busid);
				List<QuotParts> qresult=qry.list();
				return qresult;
			}
			catch (Exception e) {
				throw e;
			}
			finally {
				session.close();
			}

		}
		
		public  List<QuotParts> getParts(String qtNum,int busid) throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				System.out.println("In QuotParts getParts");
			     String querystr="from QuotParts p where p.quotid=(from Quotation where quotid=:qtNum and business_id=:busid) and business_id=:busid ";
			     Query query=session.createQuery(querystr);
			     query.setString("qtNum", qtNum);
			     query.setInteger("busid", busid);
			     List<QuotParts> p=query.list();
			     return p;
			}
			catch (Exception e) {
			     throw e;
			}
			finally {
			     session.close();
			}
		}
		
	}



